﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Net;
using System.Xml;
using System.Web.UI;

namespace WebPALTT.Lib
{
    public class CsvFileWriter : StreamWriter
    {
        public CsvFileWriter(Stream stream) : base(stream) { }
        public CsvFileWriter(string filename) : base(filename) { }

        public void WriteRow(List<string> row)
        {
            StringBuilder builder = new StringBuilder();
            foreach (string value in row)
            {
                if (builder.Length > 0)
                    builder.Append(',');

                if (value.IndexOfAny(new char[] { '"', ',' }) != -1)
                    builder.AppendFormat("\"{0}\"", value.Replace("\"", "\"\""));
                else
                    builder.Append(value);
            }
            WriteLine(builder.ToString());
        }
    }

    public static class Results
    {
        public static void SaveToFile(FileInfo file, TestResult results, string csspath = null)
        {
            if (file.Extension.Trim().ToLower().Equals(".xml"))
            {
                #region XML
                XmlDocument xmlDoc = new XmlDocument();
                XmlNode node_root = xmlDoc.CreateElement("TestResults");

                foreach (Scenario scene in results.Scenarios)
                {
                    List<ScenarioResults> sceneResList = results.Results.FindAll(
                        delegate(ScenarioResults item)
                        {
                            if (Guid.Equals(scene.Key, item.Key))
                                return true;
                            return false;
                        });
                    XmlNode node_scene = xmlDoc.CreateElement("Scenario");
                    XmlAttribute att_scene = xmlDoc.CreateAttribute("Name");
                    att_scene.Value = scene.Name;
                    node_scene.Attributes.Append(att_scene);
                    foreach (ScenarioResults sceneRes in sceneResList)
                    {
                        XmlNode node_res = xmlDoc.CreateElement("Result");
                        XmlAttribute att_res_runNo = xmlDoc.CreateAttribute("VirtualUser");
                        XmlAttribute att_res_dur = xmlDoc.CreateAttribute("Runtime");
                        att_res_runNo.Value = sceneRes.VirtualUserNo.ToString();
                        att_res_dur.Value = sceneRes.Duration.TotalMilliseconds.ToString();
                        node_res.Attributes.Append(att_res_runNo);
                        node_res.Attributes.Append(att_res_dur);

                        foreach (ActionResults actionRes in sceneRes.Results)
                        {
                            ActionItem action = scene.Actions.Find(
                                delegate(ActionItem item)
                                {
                                    if (Guid.Equals(actionRes.Key, item.Key))
                                        return true;
                                    return false;
                                }, true);

                            XmlNode node_action = xmlDoc.CreateElement("Action");
                            XmlAttribute att_action = xmlDoc.CreateAttribute("Name");
                            XmlAttribute att_actiontype = xmlDoc.CreateAttribute("ActionType");
                            XmlAttribute att_time = xmlDoc.CreateAttribute("Time");
                            XmlNode node_errors = xmlDoc.CreateElement("Errors");
                            XmlNode node_warnings = xmlDoc.CreateElement("Warnings");
                            XmlNode node_notes = xmlDoc.CreateElement("Notes");

                            att_action.Value = actionRes.Name;
                            att_actiontype.Value = actionRes.CustomType;
                            att_time.Value = actionRes.Results["Duration"].ToString();
                            foreach (string err in actionRes.Errors)
                            {
                                XmlNode node_error = xmlDoc.CreateElement("Error");
                                XmlAttribute att_msg = xmlDoc.CreateAttribute("Message");
                                att_msg.Value = err;
                                node_error.Attributes.Append(att_msg);
                                node_errors.AppendChild(node_error);
                            }

                            foreach (string warn in actionRes.Warnings)
                            {
                                XmlNode node_warn = xmlDoc.CreateElement("Warning");
                                XmlAttribute att_msg = xmlDoc.CreateAttribute("Message");
                                att_msg.Value = warn;
                                node_warn.Attributes.Append(att_msg);
                                node_warnings.AppendChild(node_warn);
                            }
                            foreach (string s in action.DataHeaders_Save)
                            {
                                if (actionRes.Data[s] != null)
                                {
                                    XmlNode node_note = xmlDoc.CreateElement("Data");
                                    XmlAttribute att_key = xmlDoc.CreateAttribute("Key");
                                    node_note.Value = actionRes.Data[s].ToString();
                                    att_key.Value = s;
                                    node_note.Attributes.Append(att_key);
                                    node_notes.AppendChild(node_note);
                                }
                            }

                            node_action.Attributes.Append(att_action);
                            node_action.Attributes.Append(att_actiontype);
                            node_action.Attributes.Append(att_time);
                            node_action.AppendChild(node_errors);
                            node_action.AppendChild(node_warnings);
                            node_action.AppendChild(node_notes);

                            node_res.AppendChild(node_action);
                        }
                        node_scene.AppendChild(node_res);
                    }
                    node_root.AppendChild(node_scene);
                }
                xmlDoc.AppendChild(node_root);
                xmlDoc.Save(file.FullName);
                #endregion
            }
            //else if (file.Extension.Trim().ToLower().Equals(".html"))
            //{
                #region HTML
                /*
                TextWriter writer = new StreamWriter(file.FullName);
                string[] table_headers = new string[] { "Scenario", "Action", "Action type", "Status", "Time (ms)", "Errors", "Warnings", "Notes" };

                writer.WriteLine("<html>");
                writer.WriteLine("<head>");
                if (!String.IsNullOrWhiteSpace(csspath))
                    writer.WriteLine("<link rel=\"stylesheet\" type=\"text/css\" href=\"" + csspath + "\" />");
                writer.WriteLine("<title>WebPALTT - Results</title>");
                writer.WriteLine("</head>");
                writer.WriteLine("<body>");
                writer.WriteLine("<table>");
                writer.WriteLine("<tr>");
                foreach (string header in table_headers)
                    writer.WriteLine("<th>" + header + "</th>");
                writer.WriteLine("</tr>");
                foreach (Scenario scene in results)
                {
                    writer.WriteLine("<tr>");
                    writer.WriteLine("<td>" + scene.Name + "</td>");
                    writer.WriteLine("</tr>");
                    foreach (ActionItem action in scene.Actions)
                    {
                        string[] values;// = GetSaveValues(action);
                        writer.WriteLine("<tr>");
                        writer.WriteLine("<td/>" +
                            "<td>" + values[0] + "</td>" +
                            "<td>" + values[1] + "</td>" +
                            "<td>" + values[2] + "</td>" +
                            "<td>" + values[3] + "</td>" +
                            "<td/>" +
                            "<td/>" +
                            "<td>" + values[6] + "</td>");
                        writer.WriteLine("</tr>");

                        for (int i = 0; i < Math.Max(action.Results.Warnings.Count, action.Results.Errors.Count); i++)
                        {
                            writer.WriteLine("<tr>");
                            writer.WriteLine("<td/>" +
                                "<td/>" +
                                "<td/>" +
                                "<td/>" +
                                "<td/>" +
                                "<td>" + (i < action.Results.Errors.Count ? action.Results.Errors[i] : "") + "</td>" +
                                "<td>" + (i < action.Results.Warnings.Count ? action.Results.Warnings[i] : "") + "</td>" +
                                "<td/>");
                            writer.WriteLine("</tr>");
                        }
                    }
                }
                writer.WriteLine("</table>");
                writer.WriteLine("</body>");
                writer.WriteLine("</html>");
                writer.Flush();
                writer.Close();
                 * */
                #endregion
            //}
            else // By Default write to csv file.
            {
                #region CSV
                using (CsvFileWriter writer = new CsvFileWriter(file.FullName))
                {
                    writer.WriteRow(new List<string> { "Scenario", "Action", "Action type", "Status", "Time (ms)", "Errors", "Warnings", "Notes" });
                    foreach (ScenarioResults sceneRes in results.Results)
                    {
                        Scenario scene = null;
                        for (int i = 0; i < results.Scenarios.Count; i++)
                            if (Guid.Equals(results.Scenarios[i].Key, sceneRes.Key))
                                scene = results.Scenarios[i];
                        if (scene == null)
                        {
                            writer.WriteRow(new List<string> {"Error finding Scenario information."});
                            continue;
                        }

                        writer.WriteRow(new List<string> { scene.Name + " (" + sceneRes.VirtualUserNo + ")" });
                        foreach (ActionResults actionRes in sceneRes.Results)
                        {
                            ActionItem action = scene.Actions.Find(
                                delegate(ActionItem item)
                                {
                                    if (Guid.Equals(actionRes.Key, item.Key))
                                        return true;
                                    return false;
                                }, true);
                            if (scene == null)
                            {
                                writer.WriteRow(new List<string> { "Error finding Action information." });
                                continue;
                            }
                            writer.WriteRow(new List<string> { " ", action.Name, action.CustomType, " ", actionRes.Results["Duration"].ToString() });
                            for (int j = 0; j < Math.Max(actionRes.Warnings.Count, actionRes.Errors.Count); j++)
                                writer.WriteRow(new List<string> { " ", " ", " ", " ", " ", j < actionRes.Errors.Count ? actionRes.Errors[j] : " ", j < actionRes.Warnings.Count ? actionRes.Warnings[j] : " " });
                            foreach (string s in action.DataHeaders_Save)
                            {
                                if(actionRes.Data[s] != null)
                                    writer.WriteRow(new List<string> { " ", " ", " ", " ", " ", " ", " ", s + " - " + actionRes.Data[s].ToString() });
                            }
                        }
                    }
                }
                #endregion
            }
        }

        public static void SaveToDataBase(string server, string dbname, bool useIntergrated, string uid, string pwd, TestResult results) 
        {
            using (SqlConnection connection = new SqlConnection("server=" + server + ";database=master;" +
                (useIntergrated ? "Trusted_Connection=True;" : ("Trusted_Connection=False;" + "user id=" + uid + ";" + "password=" + pwd + ";"))))
            {
                try
                {
                    InitDatabase(connection, dbname);
                    connection.Open();
                    connection.ChangeDatabase(dbname);

                    Guid guid_test = Guid.NewGuid();
                    using (SqlCommand command = new SqlCommand("INSERT INTO [TestRun] ([ID], [Name], [RunDate]) " +
                        "VALUES('" + guid_test.ToString() + "','" + results.Name + "','" + String.Format("{0:yyyy/MM/dd HH:mm:ss}", results.RunDate) + "')", connection))
                        command.ExecuteNonQuery();

                    foreach (Scenario scene in results.Scenarios)
                    {
                        using (SqlCommand command = new SqlCommand("INSERT INTO [Scenario] ([ID], [TestRun], [Name]) " +
                            "VALUES('" + scene.Key.ToString() + "','" + guid_test.ToString() + "','" + scene.Name + "')", connection))
                            command.ExecuteNonQuery();
                        foreach (ActionItem action in scene.Actions)
                        {
                            using (SqlCommand command = new SqlCommand("INSERT INTO [Action] ([ID], [Scenario], [Name], [Type]) " +
                                "VALUES('" + action.Key.ToString() + "','" + scene.Key.ToString() + "','" + action.Name + "','" + action.CustomType +
                                "')", connection))
                                command.ExecuteNonQuery();
                        }
                    }
                    foreach (ScenarioResults sceneRes in results.Results)
                    {
                        Scenario scene = null;
                        for (int i = 0; i < results.Scenarios.Count; i++)
                            if (Guid.Equals(results.Scenarios[i].Key, sceneRes.Key))
                                scene = results.Scenarios[i];
                        foreach (ActionResults actionRes in sceneRes.Results)
                        {
                            Guid new_guid = Guid.NewGuid();
                            ActionItem action = scene.Actions.Find(
                                delegate(ActionItem item)
                                {
                                    if (Guid.Equals(actionRes.Key, item.Key))
                                        return true;
                                    return false;
                                }, true);
                            using (SqlCommand command = new SqlCommand("INSERT INTO [ActionRun] ([ID], [Action], [Name], [VitualUserNo]) " +
                                "VALUES('" + new_guid.ToString() + "','" + actionRes.Key.ToString() + "','" + actionRes.Name + "','" + sceneRes.VirtualUserNo +
                                "')", connection))
                                command.ExecuteNonQuery();
                            foreach (KeyValuePair<String, Object> kvp in actionRes.Results)
                            {
                                using (SqlCommand command = new SqlCommand("INSERT INTO [Results] ([ActionRun], [Key], [Value]) " +
                                    "VALUES('" + new_guid.ToString() + "','" + kvp.Key.ToString() + "','" + kvp.Value.ToString() + "')", connection))
                                    command.ExecuteNonQuery();
                            }

                            foreach (string s in action.DataHeaders_Save)
                            {
                                if (actionRes.Data[s] != null)
                                    using (SqlCommand command = new SqlCommand("INSERT INTO [Data] ([ActionRun], [Key], [Value]) " +
                                    "VALUES('" + new_guid.ToString() + "','" + s + "','" + actionRes.Data[s].ToString() + "')", connection))
                                        command.ExecuteNonQuery();
                            }
                        }
                    }
                    connection.Close();
                }
                catch (Exception e)
                {
                    if (connection.State != System.Data.ConnectionState.Closed)
                        connection.Close();
                    throw e;
                }
            }
        }

        private static void InitDatabase(SqlConnection connection, string dbname)
        {
            if (connection.State != System.Data.ConnectionState.Open)
                connection.Open();

            bool dbexists = false;
            using (SqlCommand command = new SqlCommand("select * from master.dbo.sysdatabases where name=\'" + dbname + "\'", connection))
                if (command.ExecuteScalar() == null)
                    dbexists = false;
                else
                    dbexists = true;
            if(dbexists)
                connection.ChangeDatabase(dbname);
            else
            {
                using (SqlCommand command = new SqlCommand("CREATE DATABASE " + dbname, connection))
                {
                    command.ExecuteNonQuery();
                    connection.ChangeDatabase(dbname);

                    InitTable(connection, "TestRun", new string[] { 
                        "CREATE TABLE [dbo].[TestRun]([ID] [uniqueidentifier] NOT NULL,[Name] [varchar](50) NOT NULL," + 
	                        "[RunDate] [datetime] NOT NULL,CONSTRAINT [PK_TestRun] PRIMARY KEY CLUSTERED (" + 
	                        "[ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, " + 
                            "ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]" });
                    InitTable(connection, "Scenario", new string[] { 
                        "CREATE TABLE [dbo].[Scenario](" +
                            "[ID] [uniqueidentifier] NOT NULL,[TestRun] [uniqueidentifier] NOT NULL,[Name] [nvarchar](max) NOT NULL," +
                            "CONSTRAINT [PK_Scenario] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX  = OFF, " +
                            "STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]", 
                        "ALTER TABLE [dbo].[Scenario]  WITH CHECK ADD  CONSTRAINT [FK_Scenario_TestRun] FOREIGN KEY([TestRun])REFERENCES [dbo].[TestRun] ([ID])",
                        "ALTER TABLE [dbo].[Scenario] CHECK CONSTRAINT [FK_Scenario_TestRun]",
                        "ALTER TABLE [dbo].[Scenario] ADD  CONSTRAINT [DF_Scenario_ID]  DEFAULT (newid()) FOR [ID]" });
                    InitTable(connection, "Action", new string[] { 
                        "CREATE TABLE [dbo].[Action]([ID] [uniqueidentifier] NOT NULL,[Scenario] [uniqueidentifier] NOT NULL," + 
                            "[Name] [varchar](max) NOT NULL,[Type] [varchar](50) NOT NULL," + 
	                        "CONSTRAINT [PK_Action] PRIMARY KEY CLUSTERED (" + 
	                        "[ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON," + 
                            " ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]",
                        "ALTER TABLE [dbo].[Action]  WITH CHECK ADD  CONSTRAINT [FK_Action_Scenario] FOREIGN KEY([Scenario])" + 
                            "REFERENCES [dbo].[Scenario] ([ID])",
                        "ALTER TABLE [dbo].[Action] CHECK CONSTRAINT [FK_Action_Scenario]",
                        "ALTER TABLE [dbo].[Action] ADD  CONSTRAINT [DF_Action_ID]  DEFAULT (newid()) FOR [ID]"});
                    InitTable(connection, "ActionRun", new string[] { 
                        "CREATE TABLE [dbo].[ActionRun]([ID] [uniqueidentifier] NOT NULL,[Action] [uniqueidentifier] NOT NULL," + 
                        "[Name] [varchar](50) NOT NULL,[VitualUserNo] [int] NOT NULL, CONSTRAINT [PK_ActionRun] PRIMARY KEY CLUSTERED (" + 
	                        "[ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON," + 
                            " ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]",
                        "ALTER TABLE [dbo].[ActionRun]  WITH CHECK ADD CONSTRAINT [FK_ActionRun_Action] FOREIGN KEY([Action])" + 
                            "REFERENCES [dbo].[Action] ([ID])",
                        "ALTER TABLE [dbo].[ActionRun] CHECK CONSTRAINT [FK_ActionRun_Action]",
                        "ALTER TABLE [dbo].[ActionRun] ADD  CONSTRAINT [DF_ActionRun_ID]  DEFAULT (newid()) FOR [ID]"});
                    InitTable(connection, "Results", new string[] { 
                        "CREATE TABLE [dbo].[Results]([ActionRun] [uniqueidentifier] NOT NULL,[Key] [varchar](50) NOT NULL," + 
                            "[Value] [varchar](max) NOT NULL) ON [PRIMARY]",
                        "ALTER TABLE [dbo].[Results]  WITH CHECK ADD  CONSTRAINT [FK_Results_ActionRun] FOREIGN KEY([ActionRun])REFERENCES [dbo].[ActionRun] ([ID])",
                        "ALTER TABLE [dbo].[Results] CHECK CONSTRAINT [FK_Results_ActionRun]" });
                    InitTable(connection, "Data", new string[] { 
                        "CREATE TABLE [dbo].[Data]([ActionRun] [uniqueidentifier] NOT NULL,[Key] [varchar](50) NOT NULL," + 
                            "[Value] [varchar](max) NOT NULL) ON [PRIMARY]",
                        "ALTER TABLE [dbo].[Data]  WITH CHECK ADD  CONSTRAINT [FK_Data_ActionRun] FOREIGN KEY([ActionRun])REFERENCES [dbo].[ActionRun] ([ID])",
                        "ALTER TABLE [dbo].[Data] CHECK CONSTRAINT [FK_Data_ActionRun]" });
                }
            }
            connection.Close();
        }

        private static void InitTable(SqlConnection connection, string tbname, string[] queries)
        {
            if (connection.State != System.Data.ConnectionState.Open)
                connection.Open();

            bool exists = false;
            using (SqlCommand command = new SqlCommand("select * from sys.tables where name=\'" + tbname + "\'", connection))
                if (command.ExecuteScalar() == null)
                    exists = false;
                else
                    exists = true;
            if (exists)
                return;
            else
            {
                foreach (string sql in queries)
                {
                    using (SqlCommand command1 = new SqlCommand(sql, connection))
                        command1.ExecuteNonQuery();
                }
            }
        }
    }
}
